import { } from '@tauri-apps/plugin-fs'
import Database from '@tauri-apps/plugin-sql'

export interface NoteBook {
    id?: number
    title: string
    folder: string
    collect?: string
    mTime?: string
    type: string
}

export enum ChatViewInfoMode {
    Client = "Client",
    Server = "Server"
}

export interface ChatViewInfo {
    id?: number
    time: string
    message: string
    mode: ChatViewInfoMode
}

export class SqliteDatabase {
    private database!: Database
    private tableName: string = 'notebook'
    constructor(tableName: string) {
        this.tableName = tableName
    }

    async open() {
        this.database = await Database.load('sqlite:sqlite.db')
    }

    async createTable() {
        let sql = ''
        if (this.tableName === 'notebook')
            sql = `create table if not exists ${this.tableName}(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            folder TEXT NOT NULL,
            collect CHAR(2) NOT NULL,
            mTime TEXT NOT NULL,
                    type TEXT NOT NULL)`
        else if (this.tableName === 'chatview')
            sql = `create table if not exists ${this.tableName}(
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    time TEXT NOT NULL,
                    message TEXT NOT NULL,
                    mode TEXT NOT NULL)`
        await this.database.execute(sql)
    }

    async queryAllData() {
        return await this.database.select(`SELECT * FROM ${this.tableName}`);
    }

    async queryData(condition: Object) {
        return await this.database.select(`SELECT * FROM ${this.tableName} WHERE 
            ${Object.keys(condition)[0]}="${Object.values(condition)[0]}"`);
    }

    async queryDataAny(condition: NoteBook) {
        // "SELECT * from todos WHERE id = $1", [ id ]
        const keys = Object.keys(condition).filter(item => { return item !== 'id' })
        const values = Object.values(condition).filter(item => { return item !== condition.id })
        // 多个条件查询
        let sql = `SELECT * FROM ${this.tableName} WHERE `
        keys.forEach((key, index) => {
            if (index == 0)
                sql += `${key}=$${index + 1} `
            else
                sql += `AND ${key}=$${index + 1} `
        })
        const res = (await this.database.select(sql, values)) as Array<NoteBook>
        return res
    }

    async close() {
        return await this.database.close()
    }

    
    async insertData<T>(content: T) {
        const keys = Object.keys(content)
        const values = Object.values(content)
        let sql = `INSERT INTO ${this.tableName} (${keys.join(', ')}) VALUES (${values.map((_, index) => `$${index + 1}`).join(', ')})`
        return await this.database.execute(sql, values)
    }

    async updateData(id: number, content: Object) {
        const keys = Object.keys(content)
        const values = Object.values(content)
        values.push(id)
        
        let sql = `UPDATE ${this.tableName} SET `
        keys.forEach((key, index) => {
            if (index == 0)
                sql += `${key}=$${index + 1} `
            else
                sql += `, ${key}=$${index + 1} `
        })
        sql += `where id=$${keys.length + 1}`
        return await this.database.execute(sql, values)
    }

    async removeData(id: number) {
        return await this.database.execute(
            `DELETE FROM ${this.tableName} WHERE id=$1`, [id]
        )
    }
}

